from config import setting
import pymysql

# 创建数据库连接
def createConnect():
    db = pymysql.connect(host=setting.DbHost, port=setting.DbPort, user=setting.DbUser,
                         password=setting.DbPassWord, database=setting.DbName, charset=setting.DbCharset)
    return db

# 存储电影票房信息
def saveRankYear(year: int, data:list[dict]):
    # 创建数据库连接
    db = createConnect()
    # 创建游标对象，只有创建游标对象后才能进行mysql语句运行
    cursor = db.cursor()
    # 创建数据库，如果存在则删除后再进行建表
    isExistSql = "DROP TABLE IF EXISTS `{}boxOffice`".format(year)
    # 执行mysql语句
    cursor.execute(isExistSql)
    # 提交事务
    db.commit()
    # 创建表
    vcreateSql = "\
    CREATE TABLE `{}boxOffice` (\
    `id` int(11) NOT NULL AUTO_INCREMENT,\
    `year` int(11) NOT NULL,\
    `name` varchar(255) NOT NULL,\
    `boxOffice` float NOT NULL,\
    PRIMARY KEY (`id`)\
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;\
    ".format(year)
    cursor.execute(vcreateSql)
    db.commit()
    # 添加数据
    print('正在存储第{}年的数据'.format(year))
    for i in data:
        sql = "insert into {}boxOffice(year,name,boxOffice) values ({},'{}',{})".format(year,i['year'],i['name'],i['boxoffice'])
        print(sql)
        cursor.execute(sql)
        db.commit()
    print('第{}年的数据存储完毕'.format(year))
    cursor.close()
    db.close()

# 存入全球票房前一百
def saveRank(dataList: dict):
    db = createConnect()
    cursor = db.cursor()
    isExistSql = "DROP TABLE IF EXISTS `boxOffice`"
    cursor.execute(isExistSql)
    createSql = '''
    CREATE TABLE `boxoffice` (
    `name` varchar(255) DEFAULT NULL,
    `date` int(11) DEFAULT NULL,
    `types` varchar(255) DEFAULT NULL,
    `direct` varchar(255) DEFAULT NULL,
    `boxoffice` varchar(255) DEFAULT NULL,
    `id` int(11) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
    '''
    cursor.execute(createSql)
    db.commit()
    for data in dataList:
        sql = "insert into boxOffice(id,name,date,types,direct,boxoffice) values ({},'{}',{},'{}','{}','{}')".format(
            data['order'], data['name'], data['date'], data['types'], data['direct'], data['boxoffice'])
        cursor.execute(sql)
        db.commit()
    cursor.close()
    db.close()

# 存入Tmdb数据
def saveTmdb(data:dict):
       db = createConnect()
       cursor = db.cursor()
       # 检查数据是否已经存在
       checksql = "select * from TMDB where title = '{}'".format(
        data['title'])
       # 若已存在
       if cursor.execute(checksql):
           return

       # 添加数据
       sql = "insert into TMDB\
       (title,release_date,types,grade,budget,boxoffice,runtime,line,introduction,background,cover) values\
           ('{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}')"\
               .format(data['title'],data['release_date'],data['types'],data['grade'],data['budget'],data['boxoffice'],data['runtime'],data['lines'].replace("'",'dou'),data['desc'].replace('“','').replace('”','').replace("'",''),data['background'],data['cover'])
       cursor.execute(sql)
       db.commit()
       cursor.close()
       db.close()
